Snowflakeストレージ統合でS3からデータをロードしてみた
ストレージ統合利用した時のAWS側の設定を確認したかったの
SnowflakeではS3のデータロードが可能です。今回は「ストレージ統合」を利用してS3からデータをロードしてみたいと思います。本ブログの手順等は以下ドキュメントを参考にしています。
Snowflakeではフリートライアルが提供されていますので、お試しされる方は以下を参考にしてください。
Snowflake側の操作（その１）
ワークシート（Webインタフェース）または、SnowSQL（CLIクライアント）を使用して、データベースの操作など、クエリを実行することが可能です。
ここでは、SnowSQLを利用しますが、セットアップ等については割愛していますので、SnowSQLを利用されるかたは、以下を参考にしてください。
ストレージ統合の作成
S3等のクラウドストレージにアクセスするオブジェクト「ストレージ統合」を作成します。
ストレージ統合を利用することで、SnowflakeからS3へのアクセスにて永続的なクレデンシャルの使用を回避できます。S3に対するアクセスはこちらの方法が推奨されています。
CREATE STORAGE INTEGRATIONコマンドでストレージ統合を作成します。 ここでは、「test_integration」という名前で作成しています。
CREATE STORAGE INTEGRATION test_integration
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::0:role/role_name'
STORAGE_ALLOWED_LOCATIONS = ('s3://')
;
STORAGE_AWS_ROLE_ARN、STORAGE_ALLOWED_LOCATIONSの値はダミーです。
データファイルを格納したS3のパス、そのS3へアクセス可能なIAMロールの情報が必要ですが、後ほど作成します。上記ダミーのままストレージ統合を作成しました。
正常に作成できると、以下のような結果が返ってきます。
+----------------------------------------------------+ | status | |----------------------------------------------------| | Integration TEST_INTEGRATION successfully created. | +----------------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.180s
DESCRIBE INTEGRATIONコマンドで設定内容を確認します。
>DESC INTEGRATION test_integration; +---------------------------+---------------+------------------------------------------------+------------------+ | property | property_type | property_value | property_default | |---------------------------+---------------+------------------------------------------------+------------------| | ENABLED | Boolean | true | false | | STORAGE_PROVIDER | String | S3 | | | STORAGE_ALLOWED_LOCATIONS | List | s3:// | [] | | STORAGE_BLOCKED_LOCATIONS | List | | [] | | STORAGE_AWS_IAM_USER_ARN | String | arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 | | | STORAGE_AWS_ROLE_ARN | String | arn:aws:iam::0:role/role_name | | | STORAGE_AWS_EXTERNAL_ID | String | 1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw= | | | COMMENT | String | | | +---------------------------+---------------+------------------------------------------------+------------------+ 8 Row(s) produced. Time Elapsed: 1.112s
STORAGE_AWS_IAM_USER_ARN、STORAGE_AWS_EXTERNAL_IDの値を控えておきます。この後のAWS側の操作でIAMロール作成時に利用します。（値はマスクしてます）
AWS側の操作
ここからは、自身のAWSアカウント上の操作となります。
- データファイルが格納されたS3バケットにアクセス可能なIAMポリシーの作成
- Snowflake側のIAMユーザーを信頼関係に設定したIAMロール
- 作成したポリシーをアタッチ
上記、IAMポリシー、IAMロールを作成するCFnテンプレートを用意しました。
template.yml
AWSTemplateFormatVersion: 2010-09-09
Description: Create a role for Snowflake.
Parameters:
S3BucketName:
Type: String
Description: Name of the S3 bucket containing the data file.
SnowflakeUserArn:
Type: String
Description: IAM user created in Snowflake's AWS account.
SnowflakeExternalId:
Type: String
Description: External ID to establish trust.
Resources:
SnowflakeS3AccessRole:
Type: AWS::IAM::Role
Properties:
RoleName: test-snowflake-role
Path: /
AssumeRolePolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Principal:
AWS: !Sub ${SnowflakeUserArn}
Action: sts:AssumeRole
Condition:
StringEquals:
sts:ExternalId: !Sub ${SnowflakeExternalId}
ManagedPolicyArns:
- !Ref SnowflakeS3AccessPolicy
SnowflakeS3AccessPolicy:
Type: AWS::IAM::ManagedPolicy
Properties:
ManagedPolicyName: test-snowflake-policy
Path: /
PolicyDocument:
Version: 2012-10-17
Statement:
- Effect: Allow
Action:
- s3:PutObject
- s3:GetObject
- s3:GetObjectVersion
- s3:DeleteObject
- s3:DeleteObjectVersion
Resource: !Sub arn:aws:s3:::${S3BucketName}/*
- Effect: Allow
Action:
- s3:ListBucket
- s3:GetBucketLocation
Resource: !Sub arn:aws:s3:::${S3BucketName}
Outputs:
SnowflakeS3AccessRoleArn:
Value: !GetAtt SnowflakeS3AccessRole.Arn
CFnスタックを作成します。
> aws cloudformation deploy \
--stack-name test-snowflake-iam \
--template-file ./template.yml \
--capabilities CAPABILITY_NAMED_IAM \
--parameter-overrides \
S3BucketName=test-snowflake-datafile \
SnowflakeUserArn=arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 \
SnowflakeExternalId=1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw=
Waiting for changeset to be created..
Waiting for stack create/update to complete
Successfully created/updated stack - test-snowflake-iam
スタックの作成が完了したら、作成されたIAMロールのARNを確認します。この値でストレージ統合の値を変更します。（値はマスクしてます）
> aws cloudformation describe-stacks \ --stack-name test-snowflake-iam \ --query "Stacks[].Outputs[].OutputValue" \ --output table -------------------------------------------------------- | DescribeStacks | +------------------------------------------------------+ | arn:aws:iam::77777xxxxxx:role/test-snowflake-role | +------------------------------------------------------+
なお、ロードするデータファイルが格納されたS3バケットは既に存在していることにしています。
toshima,nanamaru nazo,fukuro somefuku,chan somei,yoshino
Snowflake側の操作（その２）
ストレージ統合更新
Snowflakeのストレージ統合のSTORAGE_AWS_ROLE_ARN、STORAGE_ALLOWED_LOCATIONSを更新します。ALTER STORAGE INTEGRATIONコマンドを実行します。
ALTER STORAGE INTEGRATION test_integration SET STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::77777xxxxxx:role/test-snowflake-role';
ALTER STORAGE INTEGRATION test_integration SET STORAGE_ALLOWED_LOCATIONS = ('s3://test-snowflake-datafile/');
修正内容を確認します。
>DESC INTEGRATION test_integration; +---------------------------+---------------+----------------------------------------------------+------------------+ | property | property_type | property_value | property_default | |---------------------------+---------------+----------------------------------------------------+------------------| | ENABLED | Boolean | true | false | | STORAGE_PROVIDER | String | S3 | | | STORAGE_ALLOWED_LOCATIONS | List | s3://test-snowflake-datafile/ | [] | | STORAGE_BLOCKED_LOCATIONS | List | | [] | | STORAGE_AWS_IAM_USER_ARN | String | arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 | | | STORAGE_AWS_ROLE_ARN | String | arn:aws:iam::77777xxxxxx:role/test-snowflake-role | | | STORAGE_AWS_EXTERNAL_ID | String | 1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw= | | | COMMENT | String | | | +---------------------------+---------------+----------------------------------------------------+------------------+ 8 Row(s) produced. Time Elapsed: 0.809s
データベース、テーブル作成
S3データをロードする、データベースとテーブルを作成します。CREATE DATABASE、CREATE TABLEコマンドを使用します。
create database testdb; create table testtable (first VARCHAR,last VARCHAR);
外部ステージ作成
データをロードする際のファイル置き場となるステージを作成します。データファイルはクラウド（S3バケット）にありますので、外部ステージを作成します。
CREATE STAGEコマンドで作成します。
CREATE STAGE test_s3_stage STORAGE_INTEGRATION = test_integration URL = 's3://test-snowflake-datafile/';
データロード
データをロードするには、仮想ウェアハウスが必要です。
ここではデフォルトで作成されているCOMPUTE_WHを使用します。SHOW WAREHOUSESコマンドで状態を確認します。
>show WAREHOUSES; +------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ | name | state | type | size | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on | resumed_on | updated_on | owner | comment | resource_monitor | actives | pendings | failed | suspended | uuid | scaling_policy | |------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------| | COMPUTE_WH | SUSPENDED | STANDARD | X-Small | 1 | 1 | 0 | 0 | 0 | Y | Y | 600 | true | | | | | 2022-07-04 17:19:04.140 -0700 | 2022-07-06 08:46:33.378 -0700 | 2022-07-06 08:46:33.378 -0700 | SYSADMIN | | null | 0 | 0 | 0 | 1 | 8122628 | STANDARD | +------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ 1 Row(s) produced. Time Elapsed: 0.130s
停止状態であればALTER WAREHOUSEで起動します。
>alter warehouse COMPUTE_WH resume; +----------------------------------+ | status | |----------------------------------| | Statement executed successfully. | +----------------------------------+ 1 Row(s) produced. Time Elapsed: 0.298s >show WAREHOUSES; +------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ | name | state | type | size | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on | resumed_on | updated_on | owner | comment | resource_monitor | actives | pendings | failed | suspended | uuid | scaling_policy | |------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------| | COMPUTE_WH | STARTED | STANDARD | X-Small | 1 | 1 | 1 | 0 | 0 | Y | Y | 600 | true | 100 | 0 | 0 | 0 | 2022-07-04 17:19:04.140 -0700 | 2022-07-06 21:20:55.731 -0700 | 2022-07-06 21:20:55.731 -0700 | SYSADMIN | | null | 1 | 0 | 0 | 0 | 8122628 | STANDARD | +------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ 1 Row(s) produced. Time Elapsed: 0.172s
COPY INTOコマンドでデータをロードします。
>copy into testtable from @test_s3_stage pattern='testdata.csv'; +-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name | |-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | s3://test-snowflake-datafile/testdata.csv | LOADED | 4 | 4 | 1 | 0 | NULL | NULL | NULL | NULL | +-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ 1 Row(s) produced. Time Elapsed: 3.511s
ロードしたデータを確認します。
>select * from testtable; +----------+----------+ | FIRST | LAST | |----------+----------| | toshima | nanamaru | | nazo | fukuro | | somefuku | chan | | somei | yoshino | +----------+----------+ 4 Row(s) produced. Time Elapsed: 0.321s
さいごに
S3のデータロードについては、冒頭であげたドキュメント等ありますが、少々読み取れない部分があったので実際にやってみてました。誰かのお役に立てれば幸いです。
![[新機能]dbtのdata testで任意の設定をconfigで指定できるようになり、各data testでSnowflakeのウェアハウスを指定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-f846b6052d1f7b983172226c6b1ed44b/6b4729aabfb5b7a3359acc27dc6b5453/dbt-1200x630-1.jpg)
